<?php
/**
 * Compare class
 *
 * Use this class for starting DB & Table compares in MySQLcompare
 *
 * PHP version 5
 *
 * Copyright (C) 2012 Ewald van Gemert (ewald@vangee.nl)
 * Permission is hereby granted, free of charge, to any person obtaining a copy of this software and 
 * associated documentation files (the "Software"), to deal in the Software without restriction,
 * including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense,
 * and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so,
 * subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all copies or substantial
 * portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT 
 * LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN 
 * NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
 * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE 
 * SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 *
 * @category   Development tool
 * @package    MySQLcompare
 * @author     Ewald van Gemert <ewald@vangee.nl>
 * @license    MIT 3
 * @version    SVN: 
 * @link       http://code.google.com/p/mysqlcompare
 * @since      File available since Release 0.9
 */

require_once 'compare.base.class.php';

// ###########################################################################
// Class Compare task
// ###########################################################################
class COMPARE extends COMPAREBASE {

	// Compare records of table`s that have equal number of lines
	private function compareEqCntRecords($table) {
		$resA = $this->dba->get_rows("SELECT * FROM `{$table}` ORDER BY 1");
		$resB = $this->dbb->get_rows("SELECT * FROM `{$table}` ORDER BY 1");
		$i=0;
		while ($i < count($resA)) {
			$rowA = $resA[$i];
			$rowB = $resB[$i];
			if ($rowA!=$rowB) return false;
			$i++;
		}
		return true;
	}

	private function compareDbaDbb($table) {
		$obj = array('a' => array('rows' => 0), 'b' => array('rows' => 0));
		
		$obj['a']['exists'] = in_array($table, $this->tablesa);
		$obj['b']['exists'] = in_array($table, $this->tablesb);
		
		$obj['a']['rows'] = $this->dba->num_rows("SELECT * FROM `{$table}`", QUIET);
		$obj['b']['rows'] = $this->dbb->num_rows("SELECT * FROM `{$table}`", QUIET);
		
		if ($obj['a']['exists']==false || $obj['b']['exists']==false) {
			if ($obj['a']['exists']==false) {
				$obj['a']['class'] = 'error';
				$obj['b']['class'] = '';
			} else {
				$obj['a']['class'] = '';
				$obj['b']['class'] = 'error';
			}
			$obj['reason'] = "Does not exist in both databases";
			$obj['equal'] = false;
			return $obj;
		}
		
		// Test tabel meta informatie:
		$metaA = $this->dba->tblmeta($table, QUIET);
		$metaB = $this->dbb->tblmeta($table, QUIET);

//echo "<pre>";
//print_r($metaA);
//print_r($metaB);
		$obj['a']['meta'] = $metaA;
		$obj['b']['meta'] = $metaB;
		
		if (($obj['a']['rows']==$obj['b']['rows'])&&($metaA==$metaB)) {
			// Verder testen.
			if ($obj['a']['rows']==0) {
				// Beide leeg, dus identiek!
				$obj['a']['class'] = 'success';
				$obj['b']['class'] = 'success';
				$obj['reason'] = 'Identical tables and empty';
				$obj['equal'] = true;
				return $obj;
			}

			if ($this->compareEqCntRecords($table)==false) {
				$obj['a']['class'] = 'inverse';
				$obj['b']['class'] = 'inverse';
				$obj['reason'] = 'Found different record(s)';
				$obj['equal'] = false;
				return $obj;
			}
			
			$obj['a']['class'] = 'success';
			$obj['b']['class'] = 'success';
			$obj['reason'] = 'Tables are equal';
			$obj['equal'] = true;
			return $obj;
			
		} else {
			// Verschillende tabel of aantal regels
			if ($obj['a']['meta']!=$obj['b']['meta']) {
				$obj['reason'] = 'Different tabledefinition';
				$obj['a']['class'] = 'warning';
				$obj['b']['class'] = 'warning';
			} else {
				$obj['reason'] = 'Number of records not equal';
				$obj['a']['class'] = 'inverse';
				$obj['b']['class'] = 'inverse';
			}
			$obj['equal'] = false;

			return $obj;
		}

		$obj['equal'] = 'unknown'; // komt niet voor!
		return $obj;
	}
	
	// Compare table data. While matching records by:
	// 1.) supplied $keys
	// 2.) PrimaryKey
	// 3.) First column
	private function deepcompareDbaDbb($table, $keys) {
		$rowsA = $this->dba->num_rows("SELECT * FROM `{$table}`", QUIET);
		$rowsB = $this->dbb->num_rows("SELECT * FROM `{$table}`", QUIET);
		
		// Test tabel informatie:
		$metaA = $this->dba->tblmeta($table, QUIET);
		$metaB = $this->dbb->tblmeta($table, QUIET);
		$interSect = $this->metaCompare($metaA, $metaB);

		$row = $this->dba->get_row("SHOW CREATE TABLE `{$table}`");
		$tblCreateA = $row[1];
		$row = $this->dbb->get_row("SHOW CREATE TABLE `{$table}`");
		$tblCreateB = $row[1];
		
		$definitionlbl = $this->html->definitionlbl(($metaA==$metaB));
		$definition = $this->html->definition($table,count($metaA),$rowsA,$this->htmlDiff($tblCreateA, $tblCreateB),count($metaB),$rowsB,$this->htmlDiff($tblCreateB, $tblCreateA));

		// ***********************************************************************************
		// Kies hier de keys waarmee de records van A en B aan elkaar gelegd gaan worden.
		//
		
		$keys = array_intersect($keys, $this->possibleKeys);
		if (count($keys)==0) $keys = $this->primaryKeys;
		if (count($keys)==0) $keys[] = $this->possibleKeys[0];

		// Read table-data from tables. 
		// - Only columns that exist in both tables
		// - Join tables by `keys` (primary or user defined)
		$objTableA = $this->dba->compareTable($table, $keys, $this->colsc);
		$objTableB = $this->dbb->compareTable($table, $keys, $this->colsc);
		
		$keyvalid = ((count($objTableA)==$rowsA)&&(count($objTableB)==$rowsB));
		$choosekey = $this->html->keyCheckbox($interSect, $table, $keys, $this->possibleKeys, $this->primaryKeys, $keyvalid);

		// Compare tables -> resulting 4 array's: different, identical, only-in-A, only-in-B
		$this->compareTableData($objTableA, $objTableB);

		// Generate column order
		$cols = $keys; // Start with all primary keys.
		// Then add everything else
		foreach ($this->colsc as $nam) if (!in_array($nam, $cols)) $cols[] = $nam;
		$trhead = $this->html->trhead($cols);

		// ************* Maak de HTML tabel met de verschillende records
		$tbldiff = $this->html->rowsDiff($this->rowsDiff, $keys, $this->colsc);

		// ************* Maak de HTML tabel met de A records
		$tblreca = $this->html->tblToHTML($this->rowsRecA, $keys, $this->colsc);

		// ************* Maak de HTML tabel met de B records
		$tblrecb = $this->html->tblToHTML($this->rowsRecB, $keys, $this->colsc);

		// ************* Maak de HTML tabel met de gelijke records
		$tblident = $this->html->tblToHTML($this->rowsIdent, $keys, $this->colsc);

		$htmlpage = $this->html->deepResult($choosekey, $definitionlbl, count($this->rowsDiff), count($this->rowsRecA), count($this->rowsRecB), count($this->rowsIdent), $definition, $trhead, $tbldiff, $tblreca, $tblrecb, $tblident);

		return $htmlpage;
	}


	private function rowcompareDbaDbb($table, $rowsel) {
		// Test tabel informatie:
		$metaA = $this->dba->tblmeta($table, QUIET);
		$metaB = $this->dbb->tblmeta($table, QUIET);
		$interSect = $this->metaCompare($metaA, $metaB);

		// ***********************************************************************************
		// Kies hier de keys waarmee de records van A en B aan elkaar gelegd gaan worden.
		$rowsel = json_decode(str_replace("\'", '"', $rowsel), true);
		$rowselsql = "";
		foreach ($rowsel[0] as $col => $val) $rowselsql .= " AND `{$col}` = '{$val}'";
		
		// Read table-row from tables. 
		$rowA = $this->dba->get_row("SELECT * FROM `{$table}` WHERE 1=1{$rowselsql}", MYSQLI_ASSOC);
		$rowB = $this->dbb->get_row("SELECT * FROM `{$table}` WHERE 1=1{$rowselsql}", MYSQLI_ASSOC);

		
		$result = $this->compareTableRow($rowA, $rowB, $metaA, $metaB);
//echo "<pre>";
//print_r($result);
		$htmlpage = $this->html->rowResult($result);

		return $htmlpage;
	}

	// ***********************************************************************
	// public functions
	// ***********************************************************************
	
	function comparedb($table) {
		if (($table=="")&&(count($this->tablesc)>0)) $table = $this->tablesc[0];
		$index = (in_array($table, $this->tablesc)) ? array_search($table, $this->tablesc) : -1;
		$obj['compare']['tablename'] = $table;
		
		ob_start();
		if ($index != -1) {
			if ($obj['compare']['result'] = $this->compareDbaDbb($table)) {
				$obj['compare']['status'] = 'OK';
			} else {
				$obj['compare']['status'] = 'Error';
			}

			$index++;
			$obj['compare']['curindex'] = $index;
			$obj['compare']['nexttable'] = (isset($this->tablesc[$index])) ? $this->tablesc[$index] : "";
			$obj['compare']['count'] = count($this->tablesc);
			$obj['compare']['progress'] = (int)(($index/count($this->tablesc))*100)."%";
			
			$obj['compare']['html'] = $this->html->compareTablesInTrRow($obj['compare']);
		} else {
			$obj['compare']['result'] = "Table does not exist";
			$obj['compare']['status'] = 'Error';
		}
		$obj['compare']['error'] = "".ob_get_clean();
			
		return $obj;
	}


	function deepcompare($table, $keys) {
		ob_start();
		$status = ($html = $this->deepcompareDbaDbb($table, $keys)) ? "OK" : "Error";
		$obj = array("deepcompare" => array("tablename"=>$table,"status"=>$status,"html"=>$html, "error"=>ob_get_clean()));
		return $obj;
	}


	function rowcompare($table, $rowsel) {
		ob_start();
		$status = ($html = $this->rowcompareDbaDbb($table, $rowsel)) ? "OK" : "Error";
		$obj = array("rowcompare" => array("tablename"=>$table,"status"=>$status,"html"=>$html, "error"=>"".ob_get_clean()));
		return $obj;
	}

}
